using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Diagnostics;
using System.Text;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using PCSComUtils.Common;
using System.Runtime.InteropServices;

namespace PCSComProduction.WorkOrder.DS
{
//	[Guid("4AB440F9-121A-4032-AA2C-0056989F3AF7")]
	public class PRO_WOBOMDS 
	{
		public PRO_WOBOMDS()
		{
		}
		private const string THIS = "PCSComProduction.Items.DS.PRO_WOBOMDS";
	
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";
			 
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS =null;
			try
			{
				PRO_WOBOMVO objObject = (PRO_WOBOMVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);
				
				strSql=	"INSERT INTO PRO_WOBOM("
				+ PRO_WOBOMTable.PRODUCTID_FLD + ","
				+ PRO_WOBOMTable.LINE_FLD + ","
				+ PRO_WOBOMTable.COMPONENTID_FLD + ","
				+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
				+ PRO_WOBOMTable.QUANTITY_FLD + ","
				+ PRO_WOBOMTable.ROUTINGID_FLD + ","
				+ PRO_WOBOMTable.SHRINK_FLD + ","
				+ PRO_WOBOMTable.BOMUMID_FLD
				+ "VALUES(?,?,?,?,?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.PRODUCTID_FLD].Value = objObject.ProductID;
				
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.PRODUCTID_FLD].Value = objObject.Line;


				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.COMPONENTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.COMPONENTID_FLD].Value = objObject.ComponentID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.LEADTIMEOFFSET_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WOBOMTable.LEADTIMEOFFSET_FLD].Value = objObject.LeadTimeOffset;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.QUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WOBOMTable.QUANTITY_FLD].Value = objObject.Quantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.ROUTINGID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.ROUTINGID_FLD].Value = objObject.RoutingID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.SHRINK_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WOBOMTable.SHRINK_FLD].Value = objObject.Shrink;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.BOMUMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.BOMUMID_FLD].Value = objObject.BomUMID;
				
				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql=	"DELETE " + PRO_WOBOMTable.TABLE_NAME + " WHERE  " + "WOBomID" + "=" + pintID.ToString();
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		public void DeleteByWO(int pintWOMasterID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				string strSql = "DELETE " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.WORKORDERMASTERID_FLD + "=" + pintWOMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		public void DeleteByWO(int pintWOMasterID, int pintWODetailID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				string strSql = "DELETE " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.WORKORDERMASTERID_FLD + "=" + pintWOMasterID
					+ " AND " + PRO_WOBOMTable.WORKORDERDETAILID_FLD + "=" + pintWODetailID;
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	
		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
				+ PRO_WOBOMTable.WOBOMID_FLD + ","
				+ PRO_WOBOMTable.PRODUCTID_FLD + ","
				+ PRO_WOBOMTable.COMPONENTID_FLD + ","
				+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
				+ PRO_WOBOMTable.QUANTITY_FLD + ","
				+ PRO_WOBOMTable.ROUTINGID_FLD + ","
				+ PRO_WOBOMTable.SHRINK_FLD + ","
				+ PRO_WOBOMTable.BOMUMID_FLD
				+ " FROM " + PRO_WOBOMTable.TABLE_NAME
				+" WHERE " + PRO_WOBOMTable.WOBOMID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_WOBOMVO objObject = new PRO_WOBOMVO();

				while (odrPCS.Read())
				{ 
				objObject.WOBomID = int.Parse(odrPCS[PRO_WOBOMTable.WOBOMID_FLD].ToString().Trim());
				objObject.ProductID = int.Parse(odrPCS[PRO_WOBOMTable.PRODUCTID_FLD].ToString().Trim());
				objObject.ComponentID = int.Parse(odrPCS[PRO_WOBOMTable.COMPONENTID_FLD].ToString().Trim());
				objObject.LeadTimeOffset = Decimal.Parse(odrPCS[PRO_WOBOMTable.LEADTIMEOFFSET_FLD].ToString().Trim());
				objObject.Quantity = Decimal.Parse(odrPCS[PRO_WOBOMTable.QUANTITY_FLD].ToString().Trim());
				objObject.RoutingID = int.Parse(odrPCS[PRO_WOBOMTable.ROUTINGID_FLD].ToString().Trim());
				objObject.Shrink = Decimal.Parse(odrPCS[PRO_WOBOMTable.SHRINK_FLD].ToString().Trim());
				try
				{
					objObject.BomUMID = int.Parse(odrPCS[PRO_WOBOMTable.BOMUMID_FLD].ToString().Trim());
				}
				catch {}
				}		
				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public object GetObjectVO(int pintProductID, int pintComponentID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + "=" + pintProductID
					+ " AND " + PRO_WOBOMTable.COMPONENTID_FLD + "=" + pintComponentID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_WOBOMVO objObject = new PRO_WOBOMVO();

				while (odrPCS.Read())
				{
					if (odrPCS[PRO_WOBOMTable.WOBOMID_FLD] != DBNull.Value)
						objObject.WOBomID = int.Parse(odrPCS[PRO_WOBOMTable.WOBOMID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.PRODUCTID_FLD] != DBNull.Value)
						objObject.ProductID = int.Parse(odrPCS[PRO_WOBOMTable.PRODUCTID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.COMPONENTID_FLD] != DBNull.Value)
						objObject.ComponentID = int.Parse(odrPCS[PRO_WOBOMTable.COMPONENTID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.LEADTIMEOFFSET_FLD] != DBNull.Value)
						objObject.LeadTimeOffset = Decimal.Parse(odrPCS[PRO_WOBOMTable.LEADTIMEOFFSET_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.QUANTITY_FLD] != DBNull.Value)
						objObject.Quantity = Decimal.Parse(odrPCS[PRO_WOBOMTable.QUANTITY_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.ROUTINGID_FLD] != DBNull.Value)
						objObject.RoutingID = int.Parse(odrPCS[PRO_WOBOMTable.ROUTINGID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.SHRINK_FLD] != DBNull.Value)
						objObject.Shrink = Decimal.Parse(odrPCS[PRO_WOBOMTable.SHRINK_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.BOMUMID_FLD] != DBNull.Value)
						objObject.BomUMID = int.Parse(odrPCS[PRO_WOBOMTable.BOMUMID_FLD].ToString().Trim());
				}
				return objObject;
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public ArrayList GetComponentOfItem(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetComponentOfItem()";
			
			ArrayList arrObjects = new ArrayList();
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				while (odrPCS.Read())
				{
					PRO_WOBOMVO objObject = new PRO_WOBOMVO();
					if (odrPCS[PRO_WOBOMTable.WOBOMID_FLD] != DBNull.Value)
						objObject.WOBomID = int.Parse(odrPCS[PRO_WOBOMTable.WOBOMID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.PRODUCTID_FLD] != DBNull.Value)
						objObject.ProductID = int.Parse(odrPCS[PRO_WOBOMTable.PRODUCTID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.COMPONENTID_FLD] != DBNull.Value)
						objObject.ComponentID = int.Parse(odrPCS[PRO_WOBOMTable.COMPONENTID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.LEADTIMEOFFSET_FLD] != DBNull.Value)
						objObject.LeadTimeOffset = Decimal.Parse(odrPCS[PRO_WOBOMTable.LEADTIMEOFFSET_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.QUANTITY_FLD] != DBNull.Value)
						objObject.Quantity = Decimal.Parse(odrPCS[PRO_WOBOMTable.QUANTITY_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.ROUTINGID_FLD] != DBNull.Value)
						objObject.RoutingID = int.Parse(odrPCS[PRO_WOBOMTable.ROUTINGID_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.SHRINK_FLD] != DBNull.Value)
						objObject.Shrink = Decimal.Parse(odrPCS[PRO_WOBOMTable.SHRINK_FLD].ToString().Trim());
					if (odrPCS[PRO_WOBOMTable.BOMUMID_FLD] != DBNull.Value)
						objObject.BomUMID = int.Parse(odrPCS[PRO_WOBOMTable.BOMUMID_FLD].ToString().Trim());
					arrObjects.Add(objObject);
				}
				arrObjects.TrimToSize();
				return arrObjects;
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public object GetObjectVO_ValidateAncestor(int pintProductID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME
					+" WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + "=" + pintProductID.ToString() 
					+ " and " + PRO_WOBOMTable.COMPONENTID_FLD + " = 0";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PRO_WOBOMVO objObject = new PRO_WOBOMVO();

				while (odrPCS.Read())
				{ 
					objObject.WOBomID = int.Parse(odrPCS[PRO_WOBOMTable.WOBOMID_FLD].ToString().Trim());
					objObject.ProductID = int.Parse(odrPCS[PRO_WOBOMTable.PRODUCTID_FLD].ToString().Trim());
					objObject.ComponentID = int.Parse(odrPCS[PRO_WOBOMTable.COMPONENTID_FLD].ToString().Trim());
					objObject.LeadTimeOffset = Decimal.Parse(odrPCS[PRO_WOBOMTable.LEADTIMEOFFSET_FLD].ToString().Trim());
					objObject.Quantity = Decimal.Parse(odrPCS[PRO_WOBOMTable.QUANTITY_FLD].ToString().Trim());
					objObject.RoutingID = int.Parse(odrPCS[PRO_WOBOMTable.ROUTINGID_FLD].ToString().Trim());
					objObject.Shrink = Decimal.Parse(odrPCS[PRO_WOBOMTable.SHRINK_FLD].ToString().Trim());
					try
					{
						objObject.BomUMID = int.Parse(odrPCS[PRO_WOBOMTable.BOMUMID_FLD].ToString().Trim());
					}
					catch {}

				}		
				return objObject;					
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PRO_WOBOMVO objObject = (PRO_WOBOMVO) pobjObjecVO;

			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql=	"UPDATE PRO_WOBOM SET "
				+ PRO_WOBOMTable.PRODUCTID_FLD + "=   ?" + ","
				+ PRO_WOBOMTable.COMPONENTID_FLD + "=   ?" + ","
				+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + "=   ?" + ","
				+ PRO_WOBOMTable.QUANTITY_FLD + "=   ?" + ","
				+ PRO_WOBOMTable.ROUTINGID_FLD + "=   ?" + ","
				+ PRO_WOBOMTable.SHRINK_FLD + "=   ?" + ","
				+ PRO_WOBOMTable.BOMUMID_FLD + "=   ?"
				+" WHERE " + PRO_WOBOMTable.WOBOMID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.PRODUCTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.PRODUCTID_FLD].Value = objObject.ProductID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.COMPONENTID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.COMPONENTID_FLD].Value = objObject.ComponentID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.LEADTIMEOFFSET_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WOBOMTable.LEADTIMEOFFSET_FLD].Value = objObject.LeadTimeOffset;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.QUANTITY_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WOBOMTable.QUANTITY_FLD].Value = objObject.Quantity;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.ROUTINGID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.ROUTINGID_FLD].Value = objObject.RoutingID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.SHRINK_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PRO_WOBOMTable.SHRINK_FLD].Value = objObject.Shrink;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.BOMUMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.BOMUMID_FLD].Value = objObject.BomUMID;
				
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WOBOMTable.WOBOMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_WOBOMTable.WOBOMID_FLD].Value = objObject.WOBomID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}

		}

		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ PRO_WOBOMTable.LINE_FLD + ","
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WOBOMTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
   				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataSet List(int pintWOMasterID)
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ PRO_WOBOMTable.LINE_FLD + ","
					+ PRO_WOBOMTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WOBOMTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ "  FROM " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.WORKORDERMASTERID_FLD + "=" + pintWOMasterID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WOBOMTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			OleDbConnection oconPCS =null;
			OleDbCommandBuilder odcbPCS ;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				string strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ PRO_WOBOMTable.LINE_FLD + ","
					+ PRO_WOBOMTable.WORKORDERMASTERID_FLD + ","
					+ PRO_WOBOMTable.WORKORDERDETAILID_FLD + ","
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ "  FROM " + PRO_WOBOMTable.TABLE_NAME;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, PRO_WOBOMTable.TABLE_NAME);
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
					}
					else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{

						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public ArrayList GetAllParent()
		{
			const string METHOD_NAME = THIS + ".GetAllParent()";

			ArrayList arrObjects = new ArrayList();
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT DISTINCT("
					+ PRO_WOBOMTable.PRODUCTID_FLD + ")"
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + " NOT IN (SELECT "
					+ PRO_WOBOMTable.COMPONENTID_FLD + " FROM " + PRO_WOBOMTable.TABLE_NAME + ")";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				while (odrPCS.Read())
				{
					arrObjects.Add(int.Parse(odrPCS[PRO_WOBOMTable.PRODUCTID_FLD].ToString()));
				}
				arrObjects.TrimToSize();
				return arrObjects;
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataTable ListBomDetailOfProduct(int pintProductID, int pintWorkorderdetailID)
		{
			const string METHOD_NAME = THIS + ".ListBomDetailOfProduct()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql=	"SELECT "
					+ PRO_WOBOMTable.WOBOMID_FLD + ","
					+ "A." + PRO_WOBOMTable.WORKORDERDETAILID_FLD + ","
					+ "A." + PRO_WOBOMTable.WORKORDERMASTERID_FLD + ","
					+ "A." + PRO_WOBOMTable.LINE_FLD + ","
					+ "A." + PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ "B." + ITM_ProductTable.CODE_FLD + ","
					+ "B." + ITM_ProductTable.DESCRIPTION_FLD + ","
					+ "B." + ITM_ProductTable.PRODUCTTYPEID_FLD + ","
					+ ITM_ProductTable.REVISION_FLD + ","
					+ ITM_ProductTable.DESIGNUMID_FLD + ","
					+ "ISNULL(" + ITM_ProductTable.RATE_FLD + ",1) Rate,"
					+ "F."+ MST_WorkCenterTable.CODE_FLD + " as WorkCenterCode ,"
					+ "C." + MST_UnitOfMeasureTable.CODE_FLD + " as StockCode,"
					+ "A." + PRO_WOBOMTable.BOMUMID_FLD + " ,"
					+ "D." + ITM_CategoryTable.CODE_FLD + " as ITM_CategoryCode,"
					+ "E." + MST_PartyTable.CODE_FLD + " as MST_PartyCode,"
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ "A." + PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ "(Select Step From ITM_Routing Where ITM_Routing.RoutingID = A.RoutingID) as Operation,"
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ "WOD." + PRO_WorkOrderMasterTable.PRODUCTIONLINEID_FLD + ","
					+ "L." + PRO_ProductionLineTable.LOCATIONID_FLD + ","
					+ "B." + ITM_ProductTable.STOCKUMID_FLD
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME + " A inner join " + ITM_ProductTable.TABLE_NAME + " B on A.ComponentID = B.ProductID "
					+ " inner join " + MST_UnitOfMeasureTable.TABLE_NAME + " C on A." + PRO_WOBOMTable.BOMUMID_FLD + " = C." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD
					+ " left join "  + ITM_CategoryTable.TABLE_NAME + " D on B." + ITM_ProductTable.CATEGORYID_FLD + " = D." + ITM_CategoryTable.CATEGORYID_FLD
					+ " left join "  + MST_PartyTable.TABLE_NAME + " E on B." + ITM_ProductTable.PRIMARYVENDORID_FLD + " = E." + MST_PartyTable.PARTYID_FLD
					+ " left join "  + MST_WorkCenterTable.TABLE_NAME + " F on B." + ITM_ProductTable.WORKCENTERID_FLD + " = F." + MST_WorkCenterTable.WORKCENTERID_FLD
					+ " inner join " + PRO_WorkOrderDetailTable.TABLE_NAME + " WOD on A." +PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD + " = WOD." + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD
					+ " and WOD." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " = A." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD
					+ " inner join " + PRO_ProductionLineTable.TABLE_NAME + " L on L." + PRO_ProductionLineTable.PRODUCTIONLINEID_FLD + " = WOD." + PRO_ProductionLineTable.PRODUCTIONLINEID_FLD
					+ " WHERE A." + PRO_WOBOMTable.PRODUCTID_FLD + " = " + pintProductID.ToString()
					+ " AND A." + PRO_WOBOMTable.WORKORDERDETAILID_FLD + "=" + pintWorkorderdetailID.ToString()
					+ " ORDER BY A.Line";				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				Debug.WriteLine(strSql);
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WOBOMTable.TABLE_NAME);

				return dstPCS.Tables[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataSet ListBomDetailOfProduct(int pintProductID, int pintWorkorderdetailID,int pintProductTypeID)
		{
			const string METHOD_NAME = THIS + ".ListBomDetailOfProduct()";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				strSql=	"SELECT "
					+ PRO_WOBOMTable.LINE_FLD + ","
					//+ "A." + PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD +" as " + PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ "B." + ITM_ProductTable.CODE_FLD + ","
					+ "B." + ITM_ProductTable.DESCRIPTION_FLD + ","
					+ "B." + ITM_ProductTable.PRODUCTTYPEID_FLD + ","
					+ ITM_ProductTable.REVISION_FLD + ","
					+ "C." + MST_UnitOfMeasureTable.CODE_FLD + " as StockCode,"
					+ "A." + PRO_WOBOMTable.BOMUMID_FLD + " as UMID ,"
					+ PRO_WOBOMTable.QUANTITY_FLD + " as Qty,"
					+ ITM_ProductTable.STOCKUMID_FLD 
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME + " A inner join " + ITM_ProductTable.TABLE_NAME + " B on A.ComponentID = B.ProductID "
					+ " inner join " + MST_UnitOfMeasureTable.TABLE_NAME + " C on A." + PRO_WOBOMTable.BOMUMID_FLD + " = C." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD
					+ " WHERE A." + PRO_WOBOMTable.PRODUCTID_FLD + " = " + pintProductID.ToString()
					+ " AND A." + PRO_WOBOMTable.WORKORDERDETAILID_FLD + "=" + pintWorkorderdetailID.ToString()
					+ " AND B." + ITM_ProductTable.PRODUCTTYPEID_FLD + "=" + pintProductTypeID.ToString()
					+ " ORDER BY Line";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,Pro_IssueSubMaterialDetailTable.TABLE_NAME);

				return dstPCS;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public void CopyBOM(int pintSourceProductID, int pintDestinationProductID)
		{
			const string METHOD_NAME = THIS + ".CopyBOM()";
			 
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS =null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);
				
				strSql=	"INSERT INTO PRO_WOBOM ( "
					+ PRO_WOBOMTable.PRODUCTID_FLD + ","
					+ PRO_WOBOMTable.LINE_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD + ")"
					+ " SELECT " 
					+ pintDestinationProductID + ","
					+ PRO_WOBOMTable.LINE_FLD + ","
					+ PRO_WOBOMTable.COMPONENTID_FLD + ","
					+ PRO_WOBOMTable.LEADTIMEOFFSET_FLD + ","
					+ PRO_WOBOMTable.QUANTITY_FLD + ","
					+ PRO_WOBOMTable.ROUTINGID_FLD + ","
					+ PRO_WOBOMTable.SHRINK_FLD + ","
					+ PRO_WOBOMTable.BOMUMID_FLD
					+ "  FROM " + PRO_WOBOMTable.TABLE_NAME 
					+ "  WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + "=" + pintSourceProductID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public decimal GetRequiredQuantity (int pintProductID, int pintComponentID)
		{
			const string METHOD_NAME = THIS + ".GetRequiredQuantity()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql=	"SELECT ISNULL("
					+ PRO_WOBOMTable.QUANTITY_FLD + ", 0)"
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME
					+" WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + "=" + pintProductID.ToString() 
					+ " AND " + PRO_WOBOMTable.COMPONENTID_FLD + "=" + pintComponentID.ToString();

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				if (objResult != null)
				{
					return decimal.Parse(objResult.ToString());
				}
				else
					return decimal.Zero;
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataTable GetTopLevelItem(int pintCCNID)
		{
			const string METHOD_NAME = THIS + ".GetTopLevelItem()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT ProductID, MakeItem, ISNULL(CostCenterRateMasterID, 0) CostCenterRateMasterID,"
					+ " Code, Description, Revision"
					// just for debug only
					//+ " FROM ITM_Product WHERE ProductID IN (1055,1017, 1054)";
					+ " FROM ITM_Product WHERE ProductID NOT IN"
					+ " (SELECT ComponentID FROM PRO_WOBOM)"
					+ " AND ITM_Product.CCNID = " + pintCCNID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataTable dtbBOM = new DataTable(ITM_ProductTable.TABLE_NAME);
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbBOM);

				return dtbBOM;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataTable GetBOMStructure(int pintCCNID)
		{
			const string METHOD_NAME = THIS + ".GetBOMStructure()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT PRO_WOBOM.ProductID AS ParentID, PRO_WOBOM.ComponentID AS ProductID,"
					+ " ISNULL(PRO_WOBOM.Quantity, 0) Quantity, Code, Description, Revision, "
					+ " ITM_Product.MakeItem, ISNULL(ITM_Product.CostCenterRateMasterID, 0) CostCenterRateMasterID"
					+ " FROM PRO_WOBOM JOIN ITM_Product ON PRO_WOBOM.ComponentID = ITM_Product.ProductID"
					+ " WHERE ITM_Product.CCNID = " + pintCCNID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataTable dtbData = new DataTable();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public string GetComponentOfItem(string pstrProductID)
		{
			const string METHOD_NAME = THIS + ".GetComponentOfItem()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql=	"SELECT "
					+ PRO_WOBOMTable.COMPONENTID_FLD
					+ " FROM " + PRO_WOBOMTable.TABLE_NAME
					+ " WHERE " + PRO_WOBOMTable.PRODUCTID_FLD + "=" + pstrProductID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();
				StringBuilder strResult = new StringBuilder();
				while (odrPCS.Read())
					strResult.Append(odrPCS[PRO_WOBOMTable.COMPONENTID_FLD].ToString()).Append(",");
				return strResult.ToString();
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataSet ListComponents()
		{
			const string METHOD_NAME = THIS + ".ListComponents()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT B.ProductID AS ParentID, P.ProductID,"
					+ " C.Code AS ITM_CategoryCode, P.Code AS PartNumber, P.Description AS PartName"
					+ " , P.Revision AS Model, U.Code AS UM, P.StockUMID"
					+ " FROM ITM_Product P JOIN PRO_WOBOM B ON P.ProductID = B.ComponentID"
					+ " LEFT JOIN ITM_Category C ON P.CategoryID = C.CategoryID"
					+ " JOIN MST_UnitOfMeasure U ON B.BomUMID = U.UnitOfMeasureID";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				DataSet dstData = new DataSet();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstData);
				return dstData;
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataSet ListComponents(int pintProductID)
		{
			const string METHOD_NAME = THIS + ".ListComponents()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT B.ProductID AS ParentID, P.ProductID,"
					+ " C.Code AS ITM_CategoryCode, P.Code AS PartNumber, P.Description AS PartName"
					+ " , P.Revision AS Model, U.Code AS UM, P.StockUMID"
					+ " FROM ITM_Product P JOIN PRO_WOBOM B ON P.ProductID = B.ComponentID"
					+ " LEFT JOIN ITM_Category C ON P.CategoryID = C.CategoryID"
					+ " JOIN MST_UnitOfMeasure U ON B.BomUMID = U.UnitOfMeasureID"
					+ " WHERE B.ProductID = " + pintProductID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				DataSet dstData = new DataSet();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstData);
				return dstData;
			}
			catch(OleDbException ex)
			{			
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataTable GetBOMStructure()
		{
			const string METHOD_NAME = THIS + ".GetBOMStructure()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT PRO_WOBOM.ProductID AS ParentID, PRO_WOBOM.ComponentID AS ProductID,"
					+ " ISNULL(PRO_WOBOM.Quantity, 0) Quantity, Code, Description, Revision, "
					+ " ITM_Product.MakeItem, ISNULL(ITM_Product.CostCenterRateMasterID, 0) CostCenterRateMasterID"
					+ " FROM PRO_WOBOM JOIN ITM_Product ON PRO_WOBOM.ComponentID = ITM_Product.ProductID";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataTable dtbData = new DataTable();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataTable GetBomToConvert()
		{
			const string METHOD_NAME = THIS + ".GetBomToConvert()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT PRO_WOBOM.ProductID AS ParentID, PRO_WOBOM.ComponentID AS ProductID,"
					+ " ISNULL(PRO_WOBOM.Quantity, 0) Quantity, ITM_Product.MakeItem, ISNULL(PRO_WOBOM.LeadTimeOffset,0) AS LeadTimeOffset,"
					+ " ISNULL(ITM_Product.LTFixedTime,0) LTFixedTime, ISNULL(ITM_Product.LTVariableTime,0) LTVariableTime,"
					+ " ITM_Product.StockUMID, ISNULL(ITM_Product.ProductionLineID,0) ProductionLineID"
					+ " FROM PRO_WOBOM JOIN ITM_Product ON PRO_WOBOM.ComponentID = ITM_Product.ProductID";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataTable dtbData = new DataTable(PRO_WOBOMTable.TABLE_NAME);
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		/// <summary>
		/// List all component of a product in work order detail
		/// </summary>
		/// <param name="pintWODetailID"></param>
		/// <returns></returns>
		public DataTable ListComponentByWODetail(int pintWODetailID)
		{
			const string METHOD_NAME = THIS + ".ListComponentByWODetail()";
			const string UM_CODE = "UMCode";
			DataSet dstPCS = new DataSet();

			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				// 28-02-2007 CanhNv: add ,PRO_WOBOM.BomID,ITM_Product.ProductTypeID
				string strSql = "SELECT  PRO_WOBOM.ComponentID, PRO_WOBOM.WOBomID, ITM_Product.ProductTypeID, PRO_WOBOM.Quantity AS RequiredQuantity,"
					+ " ITM_Product.StockUMID, ITM_Product.Code, ITM_Product.Description, ITM_Product.Revision, ITM_Product.DesignUMID, ITM_Product.Rate, "
					+ " MST_UnitOfMeasure.Code AS UMCode,"
					+ " WOD.OrderQuantity * PRO_WOBOM.Quantity +  WOD.OrderQuantity * PRO_WOBOM.Quantity* ISNULL(PRO_WOBOM.Shrink, 0)/100 "
					+ " -  ISNULL((SELECT SUM(ISNULL(PRO_ComponentScrapDetail.ScrapQuantity, 0)) "
					+ " FROM PRO_ComponentScrapMaster Inner join PRO_ComponentScrapDetail "
					+ " on PRO_ComponentScrapMaster.ComponentScrapMasterID  = PRO_ComponentScrapDetail.ComponentScrapMasterID "
					+ " Where PRO_ComponentScrapDetail.WorkOrderDetailID = WOD.WorkOrderDetailID "
					+ " and PRO_WOBOM.ComponentID = PRO_ComponentScrapDetail.ProductID), 0)"
					+ " -ISNULL((SELECT SUM(ISNULL(PRO_IssueMaterialDetail.CommitQuantity, 0)) "
					+ " FROM PRO_IssueMaterialMaster Inner join PRO_IssueMaterialDetail "
					+ " on PRO_IssueMaterialMaster.IssueMaterialMasterID  = PRO_IssueMaterialDetail.IssueMaterialMasterID "
					+ " Where PRO_IssueMaterialDetail.WorkOrderDetailID = WOD.WorkOrderDetailID "
					+ " and PRO_WOBOM.ComponentID = PRO_IssueMaterialDetail.ProductID), 0) "
					+ " -ISNULL((SELECT SUM(ISNULL(PRO_WorkOrderCompletion.CompletedQuantity, 0)) "
					+ " FROM PRO_WorkOrderCompletion "
					+ " Where PRO_WorkOrderCompletion.WorkOrderDetailID = WOD.WorkOrderDetailID), 0)*PRO_WOBOM.Quantity as ShortageQuantity  "
					+ " FROM PRO_WorkOrderDetail WOD inner join PRO_WOBOM"
					+ " on WOD.ProductID = PRO_WOBOM.ProductID AND WOD.WorkOrderDetailID = PRO_WOBOM.WorkOrderDetailID"
					+ " INNER JOIN ITM_Product on PRO_WOBOM.ComponentID= ITM_Product.ProductID"
					+ " INNER JOIN MST_UnitOfMeasure ON ITM_Product.StockUMID = MST_UnitOfMeasure.UnitOfMeasureID"
					+ " WHERE WOD.WorkOrderDetailID = " + pintWODetailID;
					//+ " AND ITM_Product.ProductTypeID NOT IN (" + (int)EnumProductType.SubMaterial + "," + (int)EnumProductType.SubMaterialProduce + ")";
						
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS,PRO_WOScheduleDetailTable.TABLE_NAME);
				
				return dstPCS.Tables[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataSet ListComponentByWODetail(string pstrWODetailID)
		{
			const string METHOD_NAME = THIS + ".ListComponentByWODetail()";
			DataSet dstData = new DataSet();
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT WorkOrderDetailID, B.ProductID, B.ComponentID, B.Quantity"
					+ " FROM PRO_WOBOM B JOIN ITM_Product P"
					+ " ON B.ComponentID = P.ProductID"
					+ " AND ISNULL(P.MakeItem,0) = 0"
					+ " WHERE WorkOrderDetailID IN (" + pstrWODetailID + ");"
					+ "\n"

					+ " SELECT ComponentID, P.Code, P.Description, P.Revision, U.Code UM,"
					+ " SUM(D.OrderQuantity * B.Quantity * ISNULL(P.Rate,1)) Quantity"
                    + " FROM PRO_WorkOrderDetail D JOIN PRO_WOBOM B ON D.WorkOrderDetailID = B.WorkOrderDetailID"
					+ " JOIN ITM_Product P ON B.ComponentID = P.ProductID"
					+ " AND ISNULL(P.MakeItem,0) = 0"
					+ " LEFT JOIN MST_UnitOfMeasure U ON P.StockUMID = U.UnitOfMeasureID"
					+ " WHERE D.WorkOrderDetailID IN (" + pstrWODetailID + ")"
					+ " GROUP BY ComponentID, P.Code, P.Description, P.Revision, U.Code;"
					+ "\n"
					
					+ " SELECT IV_BinCache.ProductID, SUM(OHQuantity - ISNULL(CommitQuantity,0)) OHQuantity"
					+ " FROM IV_BinCache JOIN MST_Bin ON IV_BinCache.BinID = MST_Bin.BinID"
					+ " WHERE IV_BinCache.ProductID IN (SELECT DISTINCT ComponentID"
                    + " FROM PRO_WorkOrderDetail D JOIN PRO_WOBOM B ON D.WorkOrderDetailID = B.WorkOrderDetailID"
					+ " JOIN ITM_Product P ON B.ComponentID = P.ProductID"
					+ " AND ISNULL(P.MakeItem,0) = 0"
					+ " WHERE D.WorkOrderDetailID IN (" + pstrWODetailID + "))"
					+ " AND BinTypeID IN (" + (int)BinTypeEnum.IN + "," + (int)BinTypeEnum.OK + ")"
					+ " GROUP BY IV_BinCache.ProductID";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstData);
				
				return dstData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public bool IsEditable(int pintWorkOrderDetailID, int pintComponentID)
		{
			const string METHOD_NAME = THIS + ".IsEditable()";
					
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = " SELECT WorkOrderDetailID FROM PRO_IssueMaterialDetail"
					+ " WHERE WorkOrderDetailID = " + pintWorkOrderDetailID
					+ " AND ProductID = " + pintComponentID

					+ " SELECT WorkOrderDetailID FROM PRO_WorkOrderCompletion"
					+ " WHERE WorkOrderDetailID = " + pintWorkOrderDetailID
					+ " AND ProductID = " + pintComponentID

					+ " SELECT WorkOrderDetailID FROM PRO_RequiredMaterialDetail"
					+ " WHERE WorkOrderDetailID = " + pintWorkOrderDetailID
					+ " AND ProductID = " + pintComponentID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				DataSet dstData = new DataSet();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstData);
				bool blnResult = true;
				foreach (DataTable dtbData in dstData.Tables)
				{
					if (dtbData.Rows.Count > 0)
					{
						blnResult = false;
						break;
					}
				}
				return blnResult;
			}		
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}					
				}				
			}
		}
	}
}
